﻿using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace SearchInFiles.Lib.OpenFile
{
    public class ReadxlsxHelper
    {
        public class Page
        {
            public StringBuilder Data = new StringBuilder();
            public string Title = string.Empty;
            public Page() { }
            public Page(string? title) { if (!string.IsNullOrEmpty(title)) Title = title; }
        }
        public List<Page> Pages = new List<Page>();
        public void ReadExcel(string filePath, params string[] sheetNames)
        {
            HashSet<string> m_sheetNames = new(sheetNames);
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, true))
            {
                IEnumerable<Sheet> sheets = doc.WorkbookPart?.Workbook.Descendants<Sheet>() ?? new Sheet[0];
                if (m_sheetNames.Count > 0)
                    sheets = sheets.Where(s => (s.Name?.HasValue ?? false) && m_sheetNames.Contains(s.Name!));

                Page page;
                string cellValue;
                SharedStringTable? stringTable = doc.WorkbookPart?.SharedStringTablePart?.SharedStringTable;
                foreach (Sheet? sheet in sheets)
                {
                    if (sheet == null || string.IsNullOrEmpty(sheet.Id))
                        continue;
                    page = new(sheet?.Name);
                    foreach (Row row in (((WorksheetPart?)doc.WorkbookPart?.GetPartById(sheet!.Id!))?.Worksheet.Descendants<Row>() ?? new Row[0]))
                    {
                        foreach (Cell cell in row)
                        {
                            cellValue = GetCellValue(cell, stringTable);
                            if (!string.IsNullOrEmpty(cellValue))
                                page.Data.AppendLine(cellValue);
                        }
                    }
                    Pages.Add(page);
                }
            }
        }
        private static string GetCellValue(Cell cell, SharedStringTable? stringTable)
        {
            string value = string.Empty;
            if (cell.ChildElements.Count == 0)
                return value;

            value = cell.CellValue?.InnerText ?? string.Empty;
            if (stringTable != null && cell.DataType != null && cell.DataType == CellValues.SharedString && int.TryParse(value, out int result))
                value = stringTable.ChildElements[result].InnerText;
            return value;
        }

    }
}
